package com.tongji.quickCRUDQ.util;

import java.util.ArrayList;
import java.util.List;

import com.tongji.quickCRUDQ.define.Column;
import com.tongji.quickCRUDQ.model.CommonBean;

public class Excel {
	private CommonBean bean;
	
	
	public Excel(CommonBean bean) {
		super();
		this.bean = bean;
	}

	public  String getExcelXml() {
		String xml = "";
		WorkSheet worksheet = new WorkSheet();
        int totalWidth =1000;// this.getColumnModel().getTotalWidth(includeHidden);
        return "<xml version='1.0' encoding='utf-8'>\n" +
        "<ss:Workbook xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:o='urn:schemas-microsoft-com:office:office'>\n" +
        "<o:DocumentProperties><o:Title>" + bean.getBeanName() + "</o:Title></o:DocumentProperties>\n" +
        "<ss:ExcelWorkbook>\n" +
        "<ss:WindowHeight>" + worksheet.height + "</ss:WindowHeight>\n" +
        "<ss:WindowWidth>" + worksheet.width + "</ss:WindowWidth>\n" +
        "<ss:ProtectStructure>False</ss:ProtectStructure>\n" +
        "<ss:ProtectWindows>False</ss:ProtectWindows>\n" +
        "</ss:ExcelWorkbook>\n" +
        "<ss:Styles>\n" +
        "<ss:Style ss:ID='Default'>\n" +
        "<ss:Alignment ss:Vertical='Top' ss:WrapText='1' />\n" +
        "<ss:Font ss:FontName='arial' ss:Size='10' />\n" +
        "<ss:Borders>\n" +
        "<ss:Border  ss:Weight='1' ss:LineStyle='Continuous' ss:Position='Top' />\n" +
        "<ss:Border  ss:Weight='1' ss:LineStyle='Continuous' ss:Position='Bottom' />\n" +
        "<ss:Border  ss:Weight='1' ss:LineStyle='Continuous' ss:Position='Left' />\n" +
        "<ss:Border  ss:Weight='1' ss:LineStyle='Continuous' ss:Position='Right' />\n" +
        "</ss:Borders>\n" +
        "<ss:Interior />\n" +
        "<ss:NumberFormat />\n" +
        "<ss:Protection />\n" +
        "</ss:Style>\n" +
        "<ss:Style ss:ID='title'>\n" +
        "<ss:Borders />\n" +
        "<ss:Font />" +
        "<ss:Alignment ss:WrapText='1' ss:Vertical='Center' ss:Horizontal='Center' />\n" +
        "<ss:NumberFormat ss:Format='@' />\n" +
        "</ss:Style>\n" +
        "<ss:Style ss:ID='headercell'>\n" +
        "<ss:Font ss:Bold='1' ss:Size='10' />\n" +
        "<ss:Alignment ss:WrapText='1' ss:Horizontal='Center' />" +
        "<ss:Interior ss:Pattern='Solid' />\n" +
//        "<ss:Interior ss:Pattern='Solid' ss:Color='#A3C9F1' />\n" +
        "</ss:Style>\n" +
        "<ss:Style ss:ID='even'>\n" +
        "<ss:Interior ss:Pattern='Solid' />\n" +
//        "<ss:Interior ss:Pattern='Solid' ss:Color='#CCFFFF' />\n" +
        "</ss:Style>\n" +
        "<ss:Style ss:Parent='even' ss:ID='evendate'>\n" +
        "<ss:NumberFormat ss:Format='yyyy-mm-dd' />\n" +
        "</ss:Style>\n" +
        "<ss:Style ss:Parent='even' ss:ID='evenint'>\n" +
        "<ss:NumberFormat ss:Format='0' />\n" +
        "</ss:Style>\n" +
        "<ss:Style ss:Parent='even' ss:ID='evenfloat'>\n" +
        "<ss:NumberFormat ss:Format='0.00' />\n" +
        "</ss:Style>\n" +
        "<ss:Style ss:ID='odd'>\n" +
        "<ss:Interior ss:Pattern='Solid'   />\n" + 
//        "<ss:Interior ss:Pattern='Solid' ss:Color='#CCCCFF' />\n" +
        "</ss:Style>\n" +
        "<ss:Style ss:Parent='odd' ss:ID='odddate'>\n" +
        "<ss:NumberFormat ss:Format='yyyy-mm-dd' />\n" +  
        "</ss:Style>" +
        "<ss:Style ss:Parent='odd' ss:ID='oddint'>\n" +
        "<ss:NumberFormat ss:Format='0' />\n" +
        "</ss:Style>\n" +
        "<ss:Style ss:Parent='odd' ss:ID='oddfloat'>\n" +
        "<ss:NumberFormat ss:Format='0.00' />\n" +
        "</ss:Style>\n" +
        "</ss:Styles>\n\n" +
        worksheet.xml +
        "\n\n</ss:Workbook>\n";
	}

	class WorkSheet {

		int height = 9000;

		int width = 0;

		String xml = "";

		public WorkSheet() {
			super();
			List cellType = new ArrayList();
			List cellTypeClass = new ArrayList();
			List cm = bean.getColumns();
			int totalWidthInPixels = 0;
			String colXml = "";
			String headerXml = "";
			int visibleColumnCountReduction = 0;
			int colCount = cm.size();
			for (int i = 0; i < colCount; i++) {
				Column c = (Column) cm.get(i);
				int w = (int) (c.getWidth()*1.5);
				totalWidthInPixels += w;
				colXml += "<ss:Column ss:AutoFitWidth='1' ss:Width='" + w + "' />\n";
				headerXml += "<ss:Cell ss:StyleID='headercell'>" + "<ss:Data ss:Type='String'>" + c.getNote() + "</ss:Data>\n"
						+ "<ss:NamedCell ss:Name='Print_Titles' /></ss:Cell>\n";
				int columnType = c.getColumnType();
				switch (columnType) {
				case Column.DATA_TYPE_NUMERIC:
					cellType.add("Number");
					cellTypeClass.add("number");
					break;
				case Column.DATA_TYPE_DATE:
					cellType.add("DateTime");
					cellTypeClass.add("date");
					break;
				default:
					cellType.add("String");
					cellTypeClass.add("");
					break;
				}
			}
			int visibleColumnCount = cellType.size();

			width = totalWidthInPixels * 30 + 50;

        // Generate worksheet header details.
			StringBuffer s=new StringBuffer();
            s.append("<ss:Worksheet ss:Name='").append(bean.getBeanName()).append("'>\n")
            .append("<ss:Names>\n")
            .append("<ss:NamedRange ss:Name='Print_Titles' ss:RefersTo=\"='").append(bean.getBeanName()+ "'!R1:R2\" />\n")
            .append("</ss:Names>\n")
            .append("<ss:Table x:FullRows='1' x:FullColumns='1'")
            .append(" ss:ExpandedColumnCount='").append(visibleColumnCount+ 2)
            .append("' ss:ExpandedRowCount='").append(bean.getSize()+ 2).append("'>\n")
            .append(colXml)
            .append("<ss:Row ss:Height='38'>\n")
            .append("<ss:Cell ss:StyleID='title' ss:MergeAcross='").append((visibleColumnCount - 1)).append("'>\n")
            .append("<ss:Data xmlns:html='http://www.w3.org/TR/REC-html40' ss:Type='String'>\n")
            .append("<html:B>"+bean.getBeanName()+"</html:B></ss:Data><ss:NamedCell ss:Name='Print_Titles' />\n")
            .append("</ss:Cell>\n")
            .append("</ss:Row>\n")
            .append("<ss:Row ss:AutoFitHeight='1'>\n")
            .append(headerXml)
            .append("</ss:Row>\n");
        
        // Generate the data rows from the data in the Store
        for (int i = 0, s1=bean.getSize(); i < s1; i++) {
            s.append("<ss:Row>\n");
            String cellClass = i%2==1 ? "odd" : "even";
            int k = 0;
            for (int j = 0; j < colCount; j++) {
            	    Column c=(Column)cm.get(j);
            	    String propertyName=c.getPropertyName().toUpperCase();
                    String v=bean.getPropertyAsString(i,propertyName);	
                    if ("None".equals(cellType.get(k))==false ) {
                    	s.append("<ss:Cell ss:StyleID='").append(cellClass + cellTypeClass.get(k)).append("'><ss:Data ss:Type='String'>")
                        .append(v).append("</ss:Data></ss:Cell>\n");
                    }
                    k++;
            }
            s.append("</ss:Row>\n");
        }

       s.append("</ss:Table>\n")
        .append("<x:WorksheetOptions>\n")
        .append("<x:PageSetup>\n")
        .append("<x:Layout x:CenterHorizontal='1' x:Orientation='Landscape' />\n")
        .append("<x:Footer x:Data='Page &amp;P of &amp;N' x:Margin='0.5' />\n")
        .append("<x:PageMargins x:Top='0.5' x:Right='0.5' x:Left='0.5' x:Bottom='0.8' />\n")
        .append("</x:PageSetup>\n")
        .append("<x:FitToPage />\n")
        .append("<x:Print>\n")
        .append("<x:PrintErrors>Blank</x:PrintErrors>\n")
        .append("<x:FitWidth>1</x:FitWidth>\n")
        .append("<x:FitHeight>32767</x:FitHeight>\n")
        .append("<x:ValidPrinterInfo />\n")
        .append("<x:VerticalResolution>600</x:VerticalResolution>\n")
        .append("</x:Print>\n")
        .append("<x:Selected />\n")
        .append("<x:DoNotDisplayGridlines />\n")
        .append("<x:ProtectObjects>False</x:ProtectObjects>\n")
        .append("<x:ProtectScenarios>False</x:ProtectScenarios>\n")
        .append("</x:WorksheetOptions>\n")
        .append("</ss:Worksheet>\n");
        xml= s.toString();
	}
		
	public String getXml() {
		return xml;
	}
	  
  }
}
